from django.db import models
from sforce.enterprise import SforceEnterpriseClient
from django.conf import settings
from django.db.models import Q
from django.db import connection, transaction
from sync.models.sync_log import SyncLog
import datetime
from django.utils.encoding import smart_str, smart_unicode 
from suds import WebFault
from django.utils.translation import ugettext as _
import pprint
pp = pprint.PrettyPrinter(indent=4)
 
class SFObject(models.Model):
    #schema=models.ForeignKey(Schema)
    local_table=models.CharField(max_length=255,null=True,blank=True)
    sync_min_date=models.DateTimeField(null=True,blank=True,verbose_name=_("earliest update date"))
    local2sf_sync=models.BooleanField(verbose_name=_("sync local database to SalesForce"))
    local2sf_interval=models.IntegerField(default=0,null=True,blank=True,verbose_name=_("sync interval in seconds"))
    local2sf_last_update=models.DateTimeField(null=True,blank=True)
    sf2local_sync=models.BooleanField(verbose_name=_("sync SalesForce to local database"))
    sf2local_interval=models.IntegerField(default=0,null=True,blank=True,verbose_name=_("sync interval in seconds"))
    sf2local_last_update=models.DateTimeField(null=True,blank=True)
    is_app=models.BooleanField()
    name = models.CharField(max_length=255)
    label = models.CharField(max_length=255,null=True,blank=True)
    labelPlural = models.CharField(max_length=255,null=True,blank=True)
    activateable = models.BooleanField()
    custom = models.BooleanField()
    createable = models.BooleanField()
    deletable = models.BooleanField()
    deprecatedAndHidden = models.BooleanField()
    feedEnabled = models.BooleanField()
    keyPrefix = models.CharField(max_length=255,null=True,blank=True)
    queryable = models.BooleanField()
    replicateable = models.BooleanField()
    mergeable = models.BooleanField()
    layoutable = models.BooleanField()
    retrieveable = models.BooleanField()
    searchable = models.BooleanField()
    triggerable = models.BooleanField()
    undeletable = models.BooleanField()
    updateable = models.BooleanField()
    class Meta:
        db_table = 'sync_sf_object'
        app_label = 'sync'
    @staticmethod
    def connect():
        con = SforceEnterpriseClient(settings.SF_ENTERPRISE_URL)
        con.login(settings.SF_USERNAME,settings.SF_PASSWORD,settings.SF_TOKEN)
        return con
    @staticmethod
    def import_objects():
        con=SFObject.connect()
        gl=con.describeGlobal()
        for r in gl.sobjects:
            name=getattr(r,'name', "")
            try:
                sf_obj=SFObject.objects.get(name=name)
            except:
                sf_obj=SFObject()
            sf_obj.set_data(r)
            sf_obj.save()
        return gl
    def set_data(self,rec):
        self.name = getattr(rec,'name', "")
        self.label = getattr(rec,'label', "")
        self.labelPlural = getattr(rec,'labelPlural', "")
        self.activateable = getattr(rec,'activateable', False)
        self.custom = getattr(rec,'custom', False)
        self.createable = getattr(rec,'createable', False)
        self.deletable = getattr(rec,'deletable', False)
        self.deprecatedAndHidden = getattr(rec,'deprecatedAndHidden', False)
        self.feedEnabled = getattr(rec,'feedEnabled', False)
        self.keyPrefix = getattr(rec,'keyPrefix', "")
        self.queryable = getattr(rec,'queryable', False)
        self.replicateable = getattr(rec,'replicateable', False)
        self.mergeable = getattr(rec,'mergeable', False)
        self.layoutable = getattr(rec,'layoutable', False)
        self.retrieveable = getattr(rec,'retrieveable', False)
        self.searchable = getattr(rec,'searchable', False)
        self.triggerable = getattr(rec,'triggerable', False)
        self.undeletable = getattr(rec,'undeletable', False)
        self.updateable = getattr(rec,'updateable', False)
    def set_elements(self):
        con=SFObject.connect()
        sfo_desc=con.describeSObject(self.name)
        if hasattr(sfo_desc,"childRelationships"):
            for r in sfo_desc.childRelationships:
                self.set_relationship(r)
        if hasattr(sfo_desc,"fields"):
            for f in sfo_desc.fields:
                self.set_field(f)
        return sfo_desc
    def set_field(self,f):
        from sync.models.sf_field import SFField
        SFField.set_field(self,f)
    def set_relationship(self,r):
        from sync.models.sf_child_relation import SFChildRelation
        SFChildRelation.set_relationship(self,r)
    def default_fields(self):
        return """
        lid int(11) NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (lid),
        lupdate tinyint(1) default 0 NOT NULL ,
        ldelete tinyint(1) default 0 NOT NULL ,
        ladd tinyint(1) default 0 NOT NULL ,
        lchange datetime NULL 
        """
        from sync.models.sf_field import SFField
        tname="lsf_%s"%self.name.lower()
    def create_table(self):
        from sync.models.sf_field import SFField
        SFField.set_default_field(self)
        tname="lsf_%s"%self.name.lower()
        fields = SFField.objects.filter(obj=self)
        fields= fields.filter(Q(is_local=True)|Q(is_default=True))
        sql=""
        for f in fields:
                sql+="%s,"%f.get_create_sql()
        print sql
        sql= sql [0:len(sql)-1]
        ctable= "CREATE TABLE %s (%s)"% (tname,sql)
        cursor=connection.cursor()
        cursor.execute(ctable) 
        self.local_table=tname
        self.save()
    def clear(self):
        #delete Table
        if self.local_table:
            try:
                cursor=connection.cursor()
                sql="DROP TABLE %s"%self.local_table
                cursor.execute(sql) 
            except:
                pass
        #delete all fields
        from sync.models.sf_field import SFField
        fields = SFField.objects.filter(obj=self)
        for f in fields:
            f.delete()
        self.local_table=""
        self.local2sf_last_update=None
        self.sf2local_last_update=None
        
        self.save()
    #sync related functions
    def list_fields(self,sf=False,createable=0,updateable=0):
        from sync.models.sf_field import SFField
        fields=SFField.objects.filter(obj=self,
                                       is_local=True)
        if sf:
            fields=fields.filter(is_sf=True)
            if createable==1:
                fields=fields.filter(is_sf=True,createable=True)
            elif  createable==2:   
                fields=fields.filter(is_sf=True,createable=False)
            if updateable==1:
                fields=fields.filter(is_sf=True,updateable=True)
            elif  updateable==2:   
                fields=fields.filter(is_sf=True,updateable=False)
        #both=[]
        sf=[]
        local=[]
        types=[]
        for f in fields:
            """
            both.append({'name':f.name,
                      'local_name':f.local_field})
            """
            sf.append(f.name)
            local.append(f.local_field)
            types.append(f.local_type)
        return local,sf,types
    
    def l_filter(self,filter='',sort='',sf=False,createable=0,updateable=0):
        local,sf,types=self.list_fields(sf,createable,updateable)
        fld=','.join(local)
        sql="""SELECT lid,Id, %s FROM %s """%(fld, self.local_table)
        if filter:
            sql+=" WHERE %s "%filter
        if sort:
            sql+=" ORDER BY %s "%sort
        print sql
        cursor=connection.cursor()
        cursor.execute(sql)
        res=cursor.fetchall() 
        records=[]
        if sf:
            for r in res:
                i=2
                rec={"lid":r[0],"Id":r[1]}
                for f in local:
                    rec[f]=r[i]
                    i+=1
                records.append(rec)
        else:
            for r in res:
                i=0
                rec={}
                for f in local:
                    rec[f]=r[i]
                    i+=0
                records.append(rec)
        return records
            
    def l_get(self,filter):
        pass
    def l_all(self):
        pass        
    def get_min_update(self):
        if self.sync_min_date:
            return self.sync_min_date
        try:
            return settings.SYNC_MIN_DATE
        except:
            return datetime.datetime(1970,1,1)
    def synch_local2sf_delete(self):
        sql="""SELECT Id,lid FROM %s WHERE ldelete=1"""%(self.local_table)
        cursor=connection.cursor()
        cursor.execute(sql)
        res=cursor.fetchall() 
        h = SFObject.connect()
        for r in res:
            f_res=h.delete(r[0])
            success=getattr(f_res,"success",False)   
            if success:
                sql="""DELETE FROM %s WHERE lid=%d"""%(self.local_table,r[1])
                print sql
                cursor=connection.cursor()
                cursor.execute(sql)
            else:
                #write log with error 
                #pp.pprint(f_res)
                log= SyncLog(lid=r[1],
                             sf_id=r[0],
                             action="local2sf_delete",
                             status=1,
                             sf_object=self.name,
                             local=False,
                             errors=1)
                log.started=log.get_utc_time()
                log.ended=log.get_utc_time()
                #TODO:Create a loop to get all errors not only the first one
                log.error_text=getattr(f_res["errors"][0],"message",'') 
                log.error_code=getattr(f_res["errors"][0],"statusCode",'') 
                log.save()

    def synch_local2sf_update(self):
        res=self.l_filter('lupdate=1','',True,0,1)
        h = SFObject.connect()
        for r in res:
            lid=None
            obj = h.generateObject(self.name)
            for k,v in r.items():
                if k=='lid':
                    lid=v
                elif v:
                    setattr(obj, k,v)
            f_res= h.update(obj)
            pp.pprint(f_res)
            success=getattr(f_res,"success",False)   
            if success:
                sql="""UPDATE %s SET lupdate=0 WHERE lid=%d"""%(self.local_table,lid)
                print sql
                cursor=connection.cursor()
                cursor.execute(sql)
            else:
                #write log with error 
                #pp.pprint(f_res)
                log= SyncLog(lid=lid,
                             sf_id=r["Id"],
                             action="local2sf_update",
                             status=1,
                             sf_object=self.name,
                             local=False,
                             errors=1)
                log.started=log.get_utc_time()
                log.ended=log.get_utc_time()
                #TODO:Create a loop to get all errors not only the first one
                log.error_text=getattr(f_res["errors"][0],"message",'') 
                log.error_code=getattr(f_res["errors"][0],"statusCode",'') 
                log.save()


    """
    synch_local2sf_add
        select fields locally
        Id must be null (ladd is redundant)
        for each record 
            create generate_object
            send to sf
            if send sucess: (has ID)
                update the Id to the record
            else:
                write a log with the error
        at the end:
            if there are more then one record to add (sucess or fail):
                write a log listing action:add, sucess count,fail count
        
    """
    def synch_local2sf_add(self):
        res=self.l_filter('ladd=1','',1)
        h = SFObject.connect()
        for r in res:
            lid=None
            obj = h.generateObject(self.name)
            for k,v in r.items():
                if k=='lid':
                    lid=v
                elif k=="Id":
                    pass
                elif v:
                    setattr(obj, k,v)
                #generate Error
            #setattr(obj, "IsDeleted",True)
            f_res= h.create(obj)
            pp.pprint(f_res)
            success=getattr(f_res,"success",False)   
            if success:
                sql="""UPDATE %s SET ladd=0,Id='%s' 
                   WHERE lid=%d"""%(self.local_table,f_res["id"],lid)
                print sql
                cursor=connection.cursor()
                cursor.execute(sql)
            else:
                #write log with error 
                pp.pprint(f_res)
                log= SyncLog(lid=lid,
                             action="local2sf_add",
                             status=1,
                             sf_object=self.name,
                             local=False,
                             errors=1)
                log.started=log.get_utc_time()
                log.ended=log.get_utc_time()
                #TODO:Create a loop to get all errors not only the first one
                log.error_text=getattr(f_res["errors"][0],"message",'') 
                log.error_code=getattr(f_res["errors"][0],"statusCode",'') 
                log.save()
            

    def synch_local2sf(self):
        #write log
        self.synch_local2sf_add()
        self.synch_local2sf_update()
        self.synch_local2sf_delete()
        self.local2sf_last_update=datetime.datetime.utcnow()
        self.save()
    def save_local(self,sfr):
        #find if the record exists locally
        sql="SELECT lid FROM %s WHERE Id='%s'"%(self.local_table,sfr.Id)
        cursor=connection.cursor()
        cursor.execute(sql)
        res=cursor.fetchall()
        try:
            lid=res[0][0]
            update=True
        except:
            update=False
        #get list of sf fields
        #TODO: get field Type 
        fields,sf,types=self.list_fields(True)
        if update:
            sql="UPDATE %s SET "%self.local_table
            for i in range(len(fields)):
                #val=getattr(sfr,fields[i],'')
                if types[i] in ("boolean",'int'):
                    val=getattr(sfr,fields[i],0)
                    sql+= " %s = %d ,"%(fields[i],val)
                elif types[i]== 'double':
                    val=getattr(sfr,fields[i],0)
                    sql+= " %s = %d ,"%(fields[i],val)
                elif types[i]== 'time':
                    val=getattr(sfr,fields[i],None)
                    if val:
                        sql+= " %s = '%s' ,"%(fields[i],val)
                    else:
                        sql+= " %s = NULL ,"%fields[i]
                elif types[i]== 'dateTime':
                    val=getattr(sfr,fields[i],None)
                    if val:
                        sql+= " %s = '%s' ,"%(fields[i],val)
                    else:
                        sql+= " %s = NULL ,"%fields[i]
                elif types[i]== 'date':
                    val=getattr(sfr,fields[i],None)
                    if val:
                        sql+= " %s = '%s' ,"%(fields[i],val)
                    else:
                        sql+= " %s = NULL ,"%fields[i]
                else:
                    val=smart_unicode(getattr(sfr,fields[i],''))
                    val = val.replace('"', '\\"')
                    val=val.replace("'", "\\'")
                    sql+= " %s = '%s' ,"%(fields[i],val)
            sql=sql[:len(sql)-1]
            sql+=" WHERE lid=%d "%lid
        else:
            local_f=','.join(fields)
            sql="INSERT INTO %s (%s) VALUES("%(self.local_table,local_f)
            for i in range(len(fields)):
                #val=smart_str(getattr(sfr,fields[i],''))
                val=getattr(sfr,fields[i],'')
                if types[i] in ("boolean",'int'):
                    try:
                        val=int(val)
                    except:
                        val=0
                    sql+= " %d ,"%val
                elif types[i]== 'double':
                    try:
                        val=float(val)
                    except:
                        val=0
                    sql+= " %d ,"%val
                elif types[i]== 'time':
                    if val:
                        sql+= " '%s' ,"%val
                    else:
                        sql+= " NULL ,"
                elif types[i]== 'dateTime':
                    if val:
                        sql+= " '%s' ,"%val
                    else:
                        sql+= " NULL ,"
                elif types[i]== 'date':
                    if val:
                        sql+= " '%s' ,"%val
                    else:
                        sql+= " NULL ,"
                else:
                    val=smart_unicode(val)
                    val = val.replace('"', '\\"')
                    val=val.replace("'", "\\'")
                    sql+= " '%s' ,"%val
            sql=sql[:len(sql)-1]
            sql+=')'
        cursor=connection.cursor()
        cursor.execute(sql)
    def delete_local(self,sf_id):
        sql="""DELETE FROM %s WHERE Id='%s'"""%(self.local_table,sf_id)
        print sql
        cursor=connection.cursor()
        cursor.execute(sql)
    def synch_sf2local(self,fromstart=False):
        h = SFObject.connect()
        log= SyncLog()
        log.sf_object= self.name
        log.local=True
        log.errors=0
        log.save()
        local,sf,types=self.list_fields(True)
        sf_fields=','.join(sf)
        q = "SELECT %s FROM %s "%(sf_fields,self.name)
        print q
        log.started=log.get_utc_time()
        res=False
        if fromstart or not self.sf2local_last_update:
            last=self.get_min_update()
        else:
            last = self.sf2local_last_update 
        q+=" WHERE LastModifiedDate >%sZ"%last.isoformat()
        #print q
        #res = h.queryAll(q)
        
        try:
            #first query save reading deleted records
            if not self.sf2local_last_update :
                res = h.query(q)
            else:    
                res = h.queryAll(q)
        except WebFault, wf:
            print wf
            log.error_code = "WebFault"
            log.error_text=wf
            log.status=1 

        log.sf_query=q
        if res:
            if hasattr(res, 'records'):
                log.rec_count = len(res.records)
                #log.save()
                for r in res.records:
                    if getattr(r,'IsDeleted', False):
                        self.delete_local(r.Id)
                    else:
                        save_res = self.save_local(r)
                        if not save_res:
                            log.errors+=1
                        else:
                            log.added+=1
            while res and (not res.done):
                res = h.queryMore(res.queryLocator)
                if hasattr(res, 'records'):
                    log.rec_count += len(res.records)
                    for r in res.records:
                        if getattr(r,'IsDeleted', False):
                            self.delete_local(r.Id)
                        else:
                            save_res = self.save_local(r)
                            if not save_res:
                                log.errors+=1
                            else:
                                log.added+=1
        log.ended=log.get_utc_time()
        log.save()
        self.sf2local_last_update=datetime.datetime.utcnow()
        self.save()
    def get_picklist_fields(self):
        from sync.models.sf_field import SFField
        return SFField.get_picklist_fields(self)
    def find_pl_field(self,dscr,fname):
        for f in dscr.fields:
            if f.name == fname:
                return f.picklistValues
        return None
    def update_pl_field(self,dscr):
        from sync.models.sf_field import SFField
        for f in dscr.fields:
            if hasattr(f,"picklistValues"):
                try:
                    lf=SFField.objects.get(name=f.name)
                    lf.picklistValues=True
                    lf.save()
                except:
                    pass
        
    def synch_picklist(self,user=None,manual=False):
        from sync.models.picklist import Picklist
        h = SFObject.connect()
        log= SyncLog(action="synch_picklist",
                     sf_object=self.name)
        log.started=log.get_utc_time()
        descr = h.describeSObject(self.name)
        self.update_pl_field(descr)
        pl_fields = self.get_picklist_fields()
        if pl_fields:
            for f in pl_fields:
                #delete older value
                list = Picklist.objects.filter(sf_object=self.name,
                                               sf_field=f)
                
                for l in list:
                    l.delete()
                #add new values
                pl_vals = self.find_pl_field(descr,f)
                if pl_vals:
                    i=0
                    for v in pl_vals:
                        
                        p = Picklist(sf_object=self.name,
                                           sf_field=f)
                        p.active=v.active
                        p.defaultValue=v.defaultValue 
                        p.label=v.label.replace("'", "\'")
                        p.value=v.value.replace("'", "\'")
                        p.position=i
                        try:
                            p.validFor=v.validFor
                        except:
                            pass
                        p.save()
                        i+=1
        log.ended=log.get_utc_time()
        log.save()

        
